package lion.framework.core.db;

import java.io.StringWriter;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Savepoint;
import java.sql.Statement;
import java.sql.Time;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Stack;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import lion.dev.exception.DBException;
import lion.dev.lang.KV;
import lion.dev.lang.MapJ;
import lion.json.Json;

/**
 * 基本类，提供了大量方便查询的方法 <br/>
 * 20130704 update 支持存储过程，但不支持传出参数返回结果集
 *
 * 20140812 update 支持枚举，枚举对应数据库中的字符串类型
 *
 * <pre>
 * time   2013 2013-4-24 上午11:27:57
 * mail   hl_0074@sina.com
 * </pre>
 *
 * @author lion
 */
public class DB implements AutoCloseable {

	private static Log log = LogFactory.getLog(DB.class);
	private int queryNums = 0;
	private Connection conn;
	private static final int SQL_TYPE_UNKNOW = Integer.MIN_VALUE;
	private boolean showSQL = false;
	private String sourceName = "default";
	private Stack<Savepoint> savepoints = new Stack<>();
	private boolean closed;

	public static final int TRANSACTION_READ_COMMITTED = Connection.TRANSACTION_READ_COMMITTED;
	public static final int TRANSACTION_READ_UNCOMMITTED = Connection.TRANSACTION_READ_UNCOMMITTED;
	public static final int TRANSACTION_REPEATABLE_READ = Connection.TRANSACTION_REPEATABLE_READ;
	public static final int TRANSACTION_SERIALIZABLE = Connection.TRANSACTION_SERIALIZABLE;

	private static final Map<Class<?>, Integer> javaSQLTypeMap = new HashMap<>();

	public DB() {

		this("default");
	}

	public DB(String sourceName) {

		this.sourceName = sourceName;
	}

	public void commit() {

		if (this.conn == null) { return; }

		try {
			if (this.getConnection().getAutoCommit()) { return; }
		} catch (Exception e) {
		}
		if (this.savepoints.isEmpty()) { return; }

		try {
			this.getConnection().commit();
		} catch (Exception e) {
			throw new DBException(e.getMessage(), e);
		} finally {
			this.savepoints.clear();
		}
	}

	public int getQueryNums() {

		return this.queryNums;
	}

	public String getType() {

		return DataSourceManager.getSourceType(this.sourceName);
	}

	public boolean haveUncommitedTrans() {

		return !this.savepoints.isEmpty();
	}

	public boolean isClosed() {

		return closed;
	}

	// transaction controll
	public void setTransaction() {

		setTransaction(-1);
	}

	public void setTransaction(int level) {

		try {
			this.getConnection().setAutoCommit(false);
			if (level > 0) {
				this.getConnection().setTransactionIsolation(level);
			}
			savepoints.add(this.getConnection().setSavepoint());
		} catch (Exception e) {
			throw new DBException(e.getMessage(), e);
		}
	}

	// querys
	public DataTable query(String sql, MapJ param) {

		NamedSQL namedSQL = new NamedSQL(sql, param);

		return this.query(namedSQL.preparedSQL(), namedSQL.getParameterValues());
	}

	public DataTable query(String sql, Object... params) {

		DataTable dt;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = this.prepareStatement(sql, params);
			rs = ps.executeQuery();
			dt = DataTable.mapResult(rs);
			queryNums++;
		} catch (Exception e) {
			throw new DBException(e.getMessage(), e);
		} finally {
			closeResultSet(rs);
			closeStatement(ps);
		}
		return dt;
	}

	/**
	 * 将每行的keycol的值作为key,valueCol列的值作为value
	 */
	public MapJ queryColMap(String sql, String keycol, String valueCol, MapJ param) {

		MapJ result = new MapJ();
		DataTable dt = this.query(sql, param);

		for (int i = 0; i < dt.getRowCount(); i++) {
			result.put(dt.getRow(i).getString(keycol), dt.getRow(i).get(valueCol));
		}

		return result;
	}

	@SuppressWarnings("unchecked")
	public <E> List<E> queryCol(String sql, MapJ param) {

		List<E> result = new ArrayList<>();
		List<MapJ> rows = this.query(sql, param).getRows();
		for (MapJ col : rows) {
			if (col.size() > 0) {
				result.add((E) col.getE(0));
			}
		}
		return result;
	}

	@SuppressWarnings("unchecked")
	public <E> List<E> queryCol(String sql, Object... params) {

		List<E> result = new ArrayList<>();
		List<MapJ> rows = this.query(sql, params).getRows();
		for (MapJ col : rows) {
			if (col.size() > 0) {
				result.add((E) col.getE(0));
			}
		}
		return result;
	}

	public MapJ queryColMap(String sql, String keycol, String valueCol, Object... params) {

		MapJ result = new MapJ();
		DataTable dt = this.query(sql, params);
		for (int i = 0; i < dt.getRowCount(); i++) {
			result.put(dt.getRow(i).getString(keycol), dt.getRow(i).get(valueCol));
		}

		return result;
	}

	public int queryInt(String sql, MapJ param) {

		DataTable dt = this.query(sql, param);

		if (dt.getRowCount() > 0) {
			MapJ row = dt.getRow(0);
			return row.getInt(row.getKey(0));
		}

		return -1;
	}

	public int queryInt(String sql, Object... params) {

		DataTable dt = this.query(sql, params);
		if (dt.getRowCount() > 0) {
			MapJ row = dt.getRow(0);
			return row.getInt(row.getKey(0));
		}
		return -1;
	}

	public <T> T queryOne(String sql, MapJ param) {

		DataTable dt = this.query(sql, param);
		if (dt.getRowCount() > 0) { return dt.get(0, 0); }

		return null;
	}

	@SuppressWarnings("unchecked")
	public <T> T queryOne(String sql, Object... params) {

		DataTable dt = this.query(sql, params);
		if (dt.getRowCount() > 0) { return (T) dt.get(0, 0); }

		return null;
	}

	public MapJ queryOneRow(String sql, MapJ param) {

		DataTable dt = this.query(sql, param);
		if (dt.getRowCount() > 0) { return dt.getRow(0); }

		return null;
	}

	public MapJ queryOneRow(String sql, Object... params) {

		DataTable dt = this.query(sql, params);
		if (dt.getRowCount() > 0) { return dt.getRow(0); }

		return null;
	}

	public boolean queryTrue(String sql, MapJ param) {

		DataTable dt = this.query(sql, param);

		if (dt.getRowCount() > 0) {
			MapJ row = dt.getRow(0);
			return row.getBoolean(row.getKey(0)) == true;
		}

		return false;
	}

	public boolean queryTrue(String sql, Object... params) {

		DataTable dt = this.query(sql, params);
		if (dt.getRowCount() > 0) {
			MapJ row = dt.getRow(0);
			return row.getBoolean(row.getKey(0)) == true;
		}

		return false;
	}

	public void rollback() {

		if (this.conn == null) { return; }
		Savepoint savepoint = null;
		if (!this.savepoints.isEmpty()) {
			savepoint = this.savepoints.pop();
		}

		try {
			if (savepoint != null) {
				this.conn.rollback(savepoint);
			} else {
				this.conn.rollback();
			}
		} catch (Exception e) {
			throw new DBException(e.getMessage(), e);
		}
	}

	/**
	 * 该方法执行命名式插入SQL语句，并返回自动生成的字段
	 */
	public DataTable save(String sql, MapJ params) {

		NamedSQL namedSQL = new NamedSQL(sql, params);

		return this.save(namedSQL.preparedSQL(), namedSQL.getParameterValues());
	}

	/**
	 * 该方法执行插入SQL语句，并返回自动生成的字段
	 */
	public DataTable save(String sql, Object... params) {

		DataTable dt = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = prepareStatement(sql, true, params);
			int hasResultset = ps.executeUpdate();
			if (hasResultset > 0) {
				rs = ps.getGeneratedKeys();
				dt = DataTable.mapResult(rs);
			}
			queryNums++;
		} catch (Exception e) {
			throw new DBException(e.getMessage(), e);
		} finally {
			closeResultSet(rs);
			closeStatement(ps);
		}
		return dt;
	}

	public void showSQL(boolean showSQL) {

		this.showSQL = showSQL;
	}

	// query update
	public int update(String table, KV<String, ?> primary, KV<?, ?>... fields) {

		StringBuilder sql = new StringBuilder();
		sql.append("update " + table + " set ");
		MapJ paramMap = new MapJ();

		int i = 0;
		for (KV<?, ?> field : fields) {
			if (i > 0) {
				sql.append(",");
			}
			sql.append(" " + field.getKey() + "=:" + field.getKey());
			paramMap.put(field.getKey().toString(), field.getValue());
			i++;
		}
		if (primary != null) {
			sql.append(" where " + primary.getKey() + "=:" + primary.getKey());
			paramMap.put(primary.getKey(), primary.getValue());
		}

		return this.update(sql.toString(), paramMap);
	}

	public int update(String sql, MapJ param) {

		NamedSQL namedSQL = new NamedSQL(sql, param);

		return this.update(namedSQL.preparedSQL(), namedSQL.getParameterValues());
	}

	public int update(String sql, Object... params) {

		PreparedStatement ps = null;
		try {
			ps = prepareStatement(sql, params);
			queryNums++;
			return ps.executeUpdate();
		} catch (Exception e) {
			throw new DBException(e.getMessage(), e);
		} finally {
			closeStatement(ps);
		}
	}

	/**
	 * 批量执行SQL
	 */
	public void updateBatch(String... sqls) {

		this.setTransaction();
		Statement statement = null;
		try {
			if (!this.getConnection().getMetaData().supportsBatchUpdates()) {
				for (String sql : sqls) {
					this.update(sql);
				}
			} else {
				statement = this.getConnection().createStatement();
				for (String sql : sqls) {
					statement.addBatch(sql);
				}
				statement.executeBatch();
			}
		} catch (Exception e) {
			throw new DBException("batch update error", e);
		} finally {
			this.commit();
			closeStatement(statement);
		}
	}

	/**
	 * 释放连接
	 */
	@Override
	public void close() {

		if (this.conn == null) { return; }
		try {
			if (this.haveUncommitedTrans()) {
				this.commit();
			}
			if (!this.conn.isClosed()) {
				this.conn.close();
			}
		} catch (Exception e) {
			log.debug(e.getMessage(), e);
		} finally {
			this.closed = true;
		}
	}

	private void closeResultSet(ResultSet result) {

		if (result != null) {
			try {
				result.close();
			} catch (Exception e) {
			}
		}
	}

	private void closeStatement(Statement statement) {

		if (statement != null) {
			try {
				statement.close();
			} catch (Exception e) {
			}
		}
	}

	private Connection getConnection() {

		if (this.conn == null) {
			this.conn = DataSourceManager.getConnection(this.sourceName);
		}

		return this.conn;
	}

	private int getParamType(Class<?> clazz) {

		Integer i = javaSQLTypeMap.get(clazz);
		if (i != null) { return i.intValue(); }

		if (Number.class.isAssignableFrom(clazz)) { return Types.NUMERIC; }

		return SQL_TYPE_UNKNOW;
	}

	private PreparedStatement prepareStatement(String sql, boolean generate, Object... params) throws Exception {

		PreparedStatement ps = null;

		if (sql.indexOf("{") > -1 && sql.indexOf("call") > 0) {
			ps = this.getConnection().prepareCall(sql);
		} else {
			ps = this.getConnection().prepareStatement(sql, generate ? Statement.RETURN_GENERATED_KEYS : Statement.NO_GENERATED_KEYS);
		}

		if (params != null && params.length > 0) {
			for (int i = 0; i < params.length; i++) {
				Object value = params[i];
				if (value == null) {
					setNull(ps, i + 1);
				} else {
					setValue(ps, i + 1, value);
				}
			}
		}

		if (showSQL && log.isDebugEnabled()) {
			log.debug("SQL:" + sql + "; " + (params != null && params.length > 0 ? Json.toJson(params) : ""));
		}

		return ps;
	}

	private PreparedStatement prepareStatement(String sql, Object... params) throws Exception {

		return this.prepareStatement(sql, false, params);
	}

	// private methods
	private void setNull(PreparedStatement ps, int i) throws SQLException {

		DatabaseMetaData dbmd = ps.getConnection().getMetaData();
		String databaseProductName = dbmd.getDatabaseProductName();
		String jdbcDriverName = dbmd.getDriverName();

		if ((databaseProductName.startsWith("DB2")) || StringUtils.startsWithAny(jdbcDriverName, new String[] { "jConnect", "SQLServer", "Apache Derby" })) {
			ps.setNull(i, Types.VARCHAR);
		} else {
			ps.setObject(i, null);
		}
	}

	private void setValue(PreparedStatement ps, int i, Object param) throws SQLException {

		Class<?> cls = param.getClass();
		int type = getParamType(cls);
		if (type != SQL_TYPE_UNKNOW) {
			ps.setObject(i, param, type);
		} else {
			if (CharSequence.class.isAssignableFrom(cls) || StringWriter.class.isAssignableFrom(cls)) {
				ps.setString(i, param.toString());
			} else if (Date.class.equals(cls) || Calendar.class.isAssignableFrom(cls)) {
				long time = 0;
				if (Date.class.equals(cls)) {
					time = ((Date) param).getTime();
				} else if (Calendar.class.isAssignableFrom(cls)) {
					time = ((Calendar) param).getTime().getTime();
				}
				ps.setTimestamp(i, new Timestamp(time));
			} else if (param.getClass().isEnum()) {
				ps.setString(i, ((Enum<?>) param).name());
			} else {
				ps.setObject(i, param);
			}
		}
	}

	static {
		javaSQLTypeMap.put(Byte.class, Types.TINYINT);
		javaSQLTypeMap.put(Byte.TYPE, Types.TINYINT);
		javaSQLTypeMap.put(Short.class, Types.SMALLINT);
		javaSQLTypeMap.put(Short.TYPE, Types.SMALLINT);
		javaSQLTypeMap.put(Integer.class, Types.INTEGER);
		javaSQLTypeMap.put(Integer.TYPE, Types.INTEGER);
		javaSQLTypeMap.put(Long.class, Types.BIGINT);
		javaSQLTypeMap.put(Long.TYPE, Types.BIGINT);
		javaSQLTypeMap.put(BigInteger.class, Types.BIGINT);
		javaSQLTypeMap.put(Float.class, Types.FLOAT);
		javaSQLTypeMap.put(Float.TYPE, Types.FLOAT);
		javaSQLTypeMap.put(Double.class, Types.DOUBLE);
		javaSQLTypeMap.put(Double.TYPE, Types.DOUBLE);
		javaSQLTypeMap.put(BigDecimal.class, Types.DECIMAL);
		javaSQLTypeMap.put(java.sql.Date.class, Types.TIMESTAMP);
		javaSQLTypeMap.put(Time.class, Types.TIME);
		javaSQLTypeMap.put(Timestamp.class, Types.TIMESTAMP);
		javaSQLTypeMap.put(Blob.class, Types.BLOB);
		javaSQLTypeMap.put(Clob.class, Types.CLOB);
		javaSQLTypeMap.put(Boolean.class, Types.TINYINT);
		javaSQLTypeMap.put(Boolean.TYPE, Types.TINYINT);
	}
}
